set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- Stored Procedure

ALTER PROCEDURE [dbo].[sprTongHopPhanTichDoanhSoNamReport]
	-- Add the parameters for the stored procedure here
	@BranchID uniqueidentifier, 
	@Year INT,
	@BrandID INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--DECLARE @vReportDate DATETIME
	--SELECT @vReportDate = dbo.Date(@Year, @Month, 1)

	DECLARE @TrungTamName NVARCHAR(255)
	DECLARE @TrungTamCode NVARCHAR(20)

	SELECT @TrungTamName = BranchName, @TrungTamCode = BranchCode FROM vwMisaBranches WHERE BranchID = @BranchID

	DECLARE @BrandName NVARCHAR(50)
	DECLARE @BrandCode NVARCHAR(50)
	SELECT @BrandCode = BrandCode, @BrandName = BrandName FROM dbo.ThuongHieu WHERE BrandID = @BrandID


	CREATE TABLE #tblMonthlyRevenue
	(
		ID INT IDENTITY(1, 1)
		,[Month] INT
		,[Year] INT
		,[DoanhSoTD] MONEY
		,[DoanhSoCC] MONEY
		,KhachHangID UNIQUEIDENTIFIER
		,ThuongHieu nvarchar(100)
	)

    -- Insert statements for procedure here
	CREATE TABLE #tblTongHopPhanTichDoanhSoNamReport
	(
		TenTrungTam NVARCHAR(255),
		KhachHangID UNIQUEIDENTIFIER,
		MaKH NVARCHAR(100),
		TenKH NVARCHAR(100),
		LEVEL INT,
		ParentKhachHangID UNIQUEIDENTIFIER,
		Nam INT,
		MucChietKhauTD DECIMAL,
		MucChietKhauCC DECIMAL,
		TenThuongHieu NVARCHAR(10),
		DuThuongID UNIQUEIDENTIFIER,
		StartDate DATETIME,
		DoanhSoCamKet MONEY,
		DoanhSoTDQuy1 MONEY,
		DoanhSoTDQuy2 MONEY,
		DoanhSoTDQuy3 MONEY,
		DoanhSoTDQuy4 MONEY,
		DoanhSoCCQuy1 MONEY,
		DoanhSoCCQuy2 MONEY,
		DoanhSoCCQuy3 MONEY,
		DoanhSoCCQuy4 MONEY
	)

		--Get all data 
		INSERT INTO #tblMonthlyRevenue(KhachHangID, [Month], [Year], [DoanhSoTD], [DoanhSoCC], ThuongHieu)
		SELECT mr.KhachHangID
		, [Thang], [Nam]
		, DoanhSoTD
		, DoanhSoCC
		,mr.ThuongHieu
		FROM vwGetTotalMonthlyRevenue mr
		INNER JOIN ThuongHieu th ON th.BrandID = @BrandID
		WHERE mr.Nam = @Year
		AND mr.ThuongHieu = th.BrandCode

		DECLARE @KhachHangID UNIQUEIDENTIFIER
		DECLARE KhachHangCursor CURSOR FOR SELECT DISTINCT KhachHangID FROM #tblMonthlyRevenue
		Open KhachHangCursor

		FETCH NEXT FROM KhachHangCursor
		INTO @KhachHangID

		WHILE @@FETCH_STATUS = 0
		BEGIN
			DECLARE @StartMonth DATETIME
			DECLARE @EndMonth DateTime
			DECLARE @DoanhSoTDQuy1 MONEY
			DECLARE @DoanhSoTDQuy2 MONEY
			DECLARE @DoanhSoTDQuy3 MONEY
			DECLARE @DoanhSoTDQuy4 MONEY
			DECLARE @DoanhSoCCQuy1 MONEY
			DECLARE @DoanhSoCCQuy2 MONEY
			DECLARE @DoanhSoCCQuy3 MONEY
			DECLARE @DoanhSoCCQuy4 MONEY
			
			SELECT @DoanhSoTDQuy1 = SUM(DoanhSoTD)
			FROM #tblMonthlyRevenue
			WHERE KhachHangID = @KhachHangID
			AND 1 <= [Month] AND [Month] < 4

			SELECT @DoanhSoTDQuy2 = SUM(DoanhSoTD)
			FROM #tblMonthlyRevenue
			WHERE KhachHangID = @KhachHangID
			AND 4 <= [Month] AND [Month] < 7

			SELECT @DoanhSoTDQuy3 = SUM(DoanhSoTD)
			FROM #tblMonthlyRevenue
			WHERE KhachHangID = @KhachHangID
			AND 7 <= [Month] AND [Month] < 10

			SELECT @DoanhSoTDQuy4 = SUM(DoanhSoTD)
			FROM #tblMonthlyRevenue
			WHERE KhachHangID = @KhachHangID
			AND 10 <= [Month] AND [Month] <= 12

			SELECT @DoanhSoCCQuy1 = SUM(DoanhSoCC)
			FROM #tblMonthlyRevenue
			WHERE KhachHangID = @KhachHangID
			AND 1 <= [Month] AND [Month] < 4

			SELECT @DoanhSoCCQuy2 = SUM(DoanhSoCC)
			FROM #tblMonthlyRevenue
			WHERE KhachHangID = @KhachHangID
			AND 4 <= [Month] AND [Month] < 7

			SELECT @DoanhSoCCQuy3 = SUM(DoanhSoCC)
			FROM #tblMonthlyRevenue
			WHERE KhachHangID = @KhachHangID
			AND 7 <= [Month] AND [Month] < 10

			SELECT @DoanhSoCCQuy4 = SUM(DoanhSoCC)
			FROM #tblMonthlyRevenue
			WHERE KhachHangID = @KhachHangID
			AND 10 <= [Month] AND [Month] <= 12	

			
			INSERT INTO #tblTongHopPhanTichDoanhSoNamReport(TenTrungTam, KhachHangID, MaKH, TenKH, LEVEL, ParentKhachHangID, Nam, TenThuongHieu,
															DoanhSoTDQuy1 ,
															DoanhSoTDQuy2,
															DoanhSoTDQuy3,
															DoanhSoTDQuy4,
															DoanhSoCCQuy1,
															DoanhSoCCQuy2,
															DoanhSoCCQuy3,
															DoanhSoCCQuy4)
			SELECT @TrungTamName AS TenTrungTam, D.KhachHangID, D.MaKH, D.TenKH, D.Level, D.ParentID AS ParentKhachHangID, @Year AS Nam, @BrandName AS TenThuongHieu,
															ISNULL(@DoanhSoTDQuy1, 0) ,
															ISNULL(@DoanhSoTDQuy2, 0),
															ISNULL(@DoanhSoTDQuy3,0) ,
															ISNULL(@DoanhSoTDQuy4,0) ,
															ISNULL(@DoanhSoCCQuy1,0) ,
															ISNULL(@DoanhSoCCQuy2,0) ,
															ISNULL(@DoanhSoCCQuy3,0) ,
															ISNULL(@DoanhSoCCQuy4,0)
			FROM vwAllCustomers D 
			WHERE d.KhachhangID = @KhachHangID
			
			FETCH NEXT FROM KhachHangCursor
			INTO @KhachHangID
		END

	--SELECT * FROM #tblMonthlyRevenue
	SELECT * FROM #tblTongHopPhanTichDoanhSoNamReport

	CLOSE KhachHangCursor;
	DEALLOCATE KhachHangCursor;
	DROP TABLE #tblTongHopPhanTichDoanhSoNamReport
	DROP TABLE #tblMonthlyRevenue

	-- exec [sprTongHopPhanTichDoanhSoNamReport] '0A110906-E0A3-46AB-B716-4F56085945D5', 2010, 1
END




